package com.guigu.standard;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 
 * @ClassName: ReadExcelUtils
 * @Description: TODO(这里用一句话描述这个类的作用) dgdg 来看酷酷
 * @author freestylewill
 * @date 2018年11月28日 下午4:39:00
 *
 */
public class ReadExcelUtils {
	private Logger logger = LoggerFactory.getLogger(ReadExcelUtils.class);
	private Workbook wb;
	private Sheet sheet;
	private Row row;

	public ReadExcelUtils(String filepath) {
		if (filepath == null) {
			return;
		}
		String ext = filepath.substring(filepath.lastIndexOf("."));
		try {
			InputStream is = new FileInputStream(filepath);
			if (".xls".equals(ext)) {
				wb = new HSSFWorkbook(is);
			} else if (".xlsx".equals(ext)) {
				wb = new XSSFWorkbook(is);
			} else {
				wb = null;
			}
		} catch (FileNotFoundException e) {
			logger.error("FileNotFoundException", e);
		} catch (IOException e) {
			logger.error("IOException", e);
		}
	}

	/**
	 * 读取Excel表格表头的内容
	 * 
	 * @param InputStream
	 * @return String 表头内容的数组
	 * @author majun
	 */
	public List<Object> readExcelTitle() throws Exception {
		if (wb == null) {
			throw new Exception("Workbook对象为空！");
		}
		sheet = wb.getSheetAt(0);
		row = sheet.getRow(0);
		// 标题总列数
		int colNum = row.getPhysicalNumberOfCells();
		System.out.println("colNum:" + colNum);
		List<Object> titleList = new ArrayList<>();
		for (int i = 0; i < colNum; i++) {
			String stringCellValue = row.getCell(i).getStringCellValue();
			titleList.add(stringCellValue);
		}
		return titleList;
	}

	/**
	 * 读取Excel数据内容
	 * 
	 * @param InputStream
	 * @return Map 包含单元格数据内容的Map对象
	 * @author majun
	 */
	public Map<Integer, Map<Integer, Object>> readExcelContent() throws Exception {
		if (wb == null) {
			throw new Exception("Workbook对象为空！");
		}
		Map<Integer, Map<Integer, Object>> content = new HashMap<Integer, Map<Integer, Object>>();
		sheet = wb.getSheetAt(0);
		// 得到总行数
		int rowNum = sheet.getLastRowNum();
		row = sheet.getRow(0);
		int colNum = row.getPhysicalNumberOfCells();
		// 正文内容应该从第二行开始,第一行为表头的标题
		for (int i = 1; i <= rowNum; i++) {
			row = sheet.getRow(i);
			int j = 0;
			Map<Integer, Object> cellValue = new HashMap<Integer, Object>();
			while (j < colNum) {
				Object obj = getCellValue(row.getCell(j));
				cellValue.put(j, obj);
				j++;
			}
			content.put(i, cellValue);
		}
		return content;
	}

	/**
	 * 读取Excel数据内容
	 * 
	 * @param InputStream
	 * @return Map 包含单元格数据内容的Map对象
	 * @author majun
	 */
	public Map<Object, Object> readExcelMapContent(int keyIndex, int valueIndex) throws Exception {
		if (wb == null) {
			throw new Exception("Workbook对象为空！");
		}
		Map<Integer, Map<Integer, Object>> content = new HashMap<Integer, Map<Integer, Object>>();
		Map<Object, Object> cellKeyValue = new HashMap<Object, Object>();
		sheet = wb.getSheetAt(0);
		// 得到总行数
		int rowNum = sheet.getLastRowNum();
		row = sheet.getRow(0);
		int colNum = row.getPhysicalNumberOfCells();
		// 正文内容应该从第二行开始,第一行为表头的标题
		for (int i = 1; i <= rowNum; i++) {
			row = sheet.getRow(i);
			int j = 0;
			// Map<Integer,Object> cellValue = new HashMap<Integer, Object>();
			Object obj = getCellValue(row.getCell(j));
			Object key = getCellValue(row.getCell(keyIndex));
			Object value = getCellValue(row.getCell(valueIndex));
			cellKeyValue.put(key, value);
			// while (j < colNum) {
			// Object obj = getCellFormatValue(row.getCell(j));
			// cellValue.put(j, obj);
			// j++;
			// }
			// content.put(i, cellValue);
		}
		return cellKeyValue;
	}

	/**
	 * 
	 * @Title: getCellValue @Description: TODO (这里用一句话描述这个方法的作用) @param cell
	 *         参数 @return Object 返回类型 @throws
	 */
	public static Object getCellValue(Cell cell) {
		if (cell == null) {
			return "";
		}
		Object obj = null;
		CellType cellType = cell.getCellType();
		switch (cellType) {
		case STRING: // 代表文本
			String stringCellValue = cell.getStringCellValue();
			stringCellValue = stringCellValue.replace(" ", "");
			stringCellValue = stringCellValue.replace("\n", "");
			stringCellValue = stringCellValue.replace("\t", "");
			obj = stringCellValue;
			System.out.print(stringCellValue + "\t");
			break;
		case BLANK: // 空白格
			String stringCellBlankValue = cell.getStringCellValue();
			obj = stringCellBlankValue;
			System.out.print(stringCellBlankValue + "\t");
			break;
		case BOOLEAN: // 布尔型
			boolean booleanCellValue = cell.getBooleanCellValue();
			obj = booleanCellValue;
			System.out.print(booleanCellValue + "\t");
			break;
		case NUMERIC: // 数字||日期
			boolean cellDateFormatted = DateUtil.isCellDateFormatted(cell);
			if (cellDateFormatted) {
				Date dateCellValue = cell.getDateCellValue();
				obj = dateCellValue;
				// System.out.print(sdf.format(dateCellValue) + "\t");
			} else {
				double numericCellValue = cell.getNumericCellValue();
				long longVal = Math.round(numericCellValue);
				Object inputValue = null;
				if (Double.parseDouble(longVal + ".0") == numericCellValue) {
					inputValue = longVal;
				} else {
					inputValue = numericCellValue;
				}
				obj = inputValue;
				System.out.print(inputValue + "\t");
			}
			break;
		case ERROR: // 错误
			byte errorCellValue = cell.getErrorCellValue();
			obj = errorCellValue;
			System.out.print(errorCellValue + "\t");
			break;
		case FORMULA: // 公式
			CellType cachedFormulaResultType = cell.getCachedFormulaResultType();
			obj = cachedFormulaResultType;
			System.out.print(cachedFormulaResultType + "\t");
			try {
				// 判断当前的cell是否为Date
				if (DateUtil.isCellDateFormatted(cell)) {
					// 如果是Date类型则，转化为Data格式
					// data格式是带时分秒的：2013-7-10 0:00:00
					// cellvalue = cell.getDateCellValue().toLocaleString();
					// data格式是不带带时分秒的：2013-7-10
					Date date = cell.getDateCellValue();
					obj = date;
				} else {
					obj = String.valueOf(cell.getStringCellValue());
				}
			} catch (IllegalStateException e) {
				String valueOf = String.valueOf(cell.getNumericCellValue());
				BigDecimal bd = new BigDecimal(Double.valueOf(valueOf));
				bd = bd.setScale(2, RoundingMode.HALF_UP);
				obj = bd;
			}
			break;
		default:
			obj = "";
			break;
		}
		return obj;
	}

	/**
	 * 
	 * @Title: getNewValue @Description: TODO 获取整数 @param value @return
	 *         String @throws
	 */
	private static String getNewValue(String value) {
		String newValue = value;
		if (value.contains(".")) {
			if (value.contains("E")) {
				int lastIndex = value.lastIndexOf("E");
				int indexNo = Integer.parseInt(value.substring(lastIndex + 1));
				String newValuString = value.substring(0, lastIndex);
				double parseDouble = Double.parseDouble(newValuString);
				int index = 1;
				for (int i = 0; i < indexNo; i++) {
					index = index * 10;
				}
				long newInt = (long) (parseDouble * index);
				newValue = String.valueOf(newInt);
				System.out.println(value + "and newValue= " + index + "and newValue= " + newValue);
			} else {
				int lastIndex = value.lastIndexOf(".");
				newValue = value.substring(0, lastIndex);
			}
		}
		return newValue;
	}

	/**
	 * 
	 * @Title: contentToTxt @Description: TODO(这里用一句话描述这个方法的作用) @param @param
	 *         filePath @param @param content 参数 @return void 返回类型 @throws
	 */
	public static void contentToTxt(String filePath, String content) {
		String oldString = new String(); // 原有txt内容
		String newString = new String();// 内容更新
		try {
			File file = new File(filePath);
			if (file.exists()) {
				System.out.print("文件存在");
			} else {
				System.out.print("文件不存在");
				file.createNewFile();// 不存在则创建
			}
			BufferedReader input = new BufferedReader(new FileReader(file));
			while ((oldString = input.readLine()) != null) {
				newString += oldString + "\n";
			}
			System.out.println(newString);
			input.close();
			newString += content;
			BufferedWriter output = new BufferedWriter(new FileWriter(file));
			output.write(newString);
			output.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private static String toQuoteString(String msg) {
		String fh = "'";
		if (!msg.contains(fh)) {
			msg = fh + msg + fh;
		}
		return msg;
	}

	public static void main(String[] args) {
		String basePath = "D:/data/excel/";
		Date currentTime = new Date();
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy_MM_dd-HH_mm_ss");
		String dateString = formatter.format(currentTime);
		String outFile = basePath + "yj_om_entity" + "_" + dateString + ".sql";
		try {
			String filepath = basePath + "20171113同步到EMR表清单.xlsx";
			ReadExcelUtils excelReader = new ReadExcelUtils(filepath);
			// 对读取Excel表格标题测试
			List<Object> title = excelReader.readExcelTitle();
			System.out.println("获得Excel表格的标题:");
			for (Object s : title) {
				System.out.print("title===" + s);
			}
			// 对读取Excel表格内容测试
			Map<Integer, Map<Integer, Object>> map = excelReader.readExcelContent();
			Map<Object, Object> readExcelMapContent = excelReader.readExcelMapContent(1, 3);
			System.out.println("获得Excel表格的内容:");
			// for (int i = 1; i <= map.size(); i++) {
			// System.out.println(map.get(i));
			// }
			StringBuilder sBuilder = new StringBuilder();
			String nextLine = "\r\n";

			for (Map.Entry<Object, Object> entry : readExcelMapContent.entrySet()) {
				// System.out.println("key= "+ entry.getKey() + "and value= " +
				// entry.getValue());
				String value = entry.getValue().toString();
				String newValue = getNewValue(value);
				String key = entry.getKey().toString();

				System.err.println("key= " + key + "and value= " + newValue);
				// String newString = "UPDATE yj_om_entity SET "+"amount"+" =
				// "+getFhMsg(newValue)+" WHERE name_en = "+getFhMsg(key)+";";
				String newString = "UPDATE  yj_om_entity SET " + "origin_table" + " = " + toQuoteString(key)
						+ " WHERE  name_en = " + toQuoteString(key) + ";";
				System.out.println(newString);
				sBuilder = sBuilder.append(newString);
				sBuilder = sBuilder.append(nextLine);
			}
			contentToTxt(outFile, sBuilder.toString());
		} catch (FileNotFoundException e) {
			System.out.println("未找到指定路径的文件!");
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}